set spark.sql.crossJoin.enabled=true;
with merge1 as (--受理网点  --1邮政工单量appeal_cnt 2.第三方量third_platform_cnt 3.网点客诉量site_cnt
    select dt
         , network_code
         , delivery_code
         , delivery_name
         , sum(if(work_type = '普通工单' and duty_proxy_code <> '888888', 1, 0))   as work_cnt              -- 呼叫中心
         , sum(if(work_type = '平台工单' and duty_proxy_code <> '888888', 1, 0))   as project_cnt           -- 平台
         , sum(if(work_type = '邮政工单' and accept_proxy_code <> '888888', 1, 0)) as appeal_cnt            -- 邮政
         , sum(if(work_type = '理赔工单' and duty_proxy_code <> '888888', 1, 0))   as claim_cnt
--          , sum(if(duty_type = '第三方' and duty_proxy_code <> '888888' and accept_proxy_code <> '888888', 1,
--                   0))                                                          as third_platform_cnt    --三方
         , sum(if(duty_type = '第三方' and ((work_type = '邮政工单' and accept_proxy_code <> '888888') or
                                         (work_type <> '邮政工单' and duty_proxy_code <> '888888')), 1,
                  0))                                                          as third_platform_cnt    --三方
         , sum(if(duty_type = '转运中心' and ((work_type = '邮政工单' and accept_proxy_code <> '888888') or
                                          (work_type <> '邮政工单' and duty_proxy_code <> '888888')), 1,
                  0))                                                          as transfer_cnt_total
         , sum(if(duty_type = '网点' and ((work_type = '邮政工单' and accept_proxy_code <> '888888') or
                                        (work_type <> '邮政工单' and duty_proxy_code <> '888888')), 1,
                  0))                                                          as site_cnt
         , sum(if((work_type = '普通工单' and second_type_code = '2104') or (work_type = '平台工单'), 1,
                  0))                                                          as xijia_sign_num        --虚假签收
         , sum(if((work_type = '普通工单' and second_type_name = '超时效催件'), 1, 0))  as over_time_num         --超时效催件
         , sum(if((work_type = '普通工单' and second_type_name = '有揽未发'), 1, 0))   as have_take_no_send_num --有揽未发
         , sum(if((work_type = '普通工单' and second_type_name = '不送货上门'), 1, 0))  as no_send_door_num      --不送货上门
         , sum(if((work_type = '普通工单' and second_type_name = '服务态度'), 1, 0))   as service_attitude_num  --服务态度
         , sum(if((work_type = '普通工单' and second_type_name = '费用问题'), 1, 0))   as money_problem_num     --费用问题
         , sum(if((work_type = '普通工单' and second_type_name = '外投意向'), 1, 0))   as out_complaint_num     --外投诉意向
         , sum(if((work_type = '普通工单' and second_type_name = '赔偿意向') or (work_type = '理赔工单'), 1,
                  0))                                                          as compensate_num        --赔偿意向
         , sum(if(reback_transfer_falg = 1, 1, 0))                             as reback_transfer_num   --退转件票数
    from jms_dm.dm_waybill_customer_complaint_detail_new_dt
    where dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
      and dt <= '{{ execution_date | cst_ds }}'
      and is_delete = 2 --未删除
    group by dt
           , network_code
           , delivery_code --派件员编码
           , delivery_name
),
     net_whole as (
         select t1.code
              , t1.name
              , t1.fran_code
              , t1.fran_name
              , t1.agent_code
              , t1.agent_name
              , t1.virt_code
              , t1.virt_name
              , t1.zone_id
              , t1.zone_code
              , t1.zone_name
              , t1.sys_area_id
              , t1.parent_area_code
              , t1.parent_area_name
              , t1.regional_id
              , t1.regional_desc
              , t1.network_type
              , t1.is_virtual_agent
              , t1.area_code
              , t1.area_name
              , t2.dt
         from (
                  --逻辑调整,去code,name,做网点类型限定
                  select *
                  from jms_dim.dim_network_whole_massage
                  where name not like '%BEST%'
              ) t1
                  join
              (
                  select date_mid_desc as dt
                  from jms_dim.dim_date_info_base
                  where date_mid_desc >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
                    and date_mid_desc <= '{{ execution_date | cst_ds }}'
              ) t2
              on 1 = 1
     )
insert
overwrite
table
jms_dm.dm_waybill_customer_complaint_delivery_agg_new_dt
partition
(
dt
)
select net1.dt                                                                         as date_time             --明细的dt,是create_time,统计的是t-1的数据
     , net1.regional_id                                                                as regional_code         --大区
     , net1.regional_desc                                                              as regional_desc         --大区
     , net1.code                                                                       as network_code          --网点
     , net1.name                                                                       as network_name          --网点
     , net1.fran_code                                                                  as fran_code             --加盟商code
     , net1.fran_name                                                                  as fran_name             --加盟商name
     , net1.agent_code                                                                 as agent_code            --代理区code
     , net1.agent_name                                                                 as agent_name            --代理区name
     , net1.virt_code                                                                  as virt_code             --虚拟代理区code
     , net1.virt_name                                                                  as virt_name             --虚拟代理区name
     , net1.zone_id                                                                    as zone_id               --网管片区负责人id
     , net1.zone_code                                                                  as zone_code             --网管片区负责人code
     , net1.zone_name                                                                  as zone_name             --网管片区负责人name
     , net1.sys_area_id                                                                as sys_area_id           --网管父片区id
     , net1.parent_area_code                                                           as parent_area_code      --网管父片区域code
     , net1.parent_area_name                                                           as parent_area_name      --网管父片区域name
     , net1.area_code                                                                  as area_code             --区域
     , net1.area_name                                                                  as area_name             --区域
     , net1.network_type                                                               as network_type          --网点类型
     , t1.delivery_code                                                                as delivery_code         --派件员
     , t1.delivery_name                                                                as delivery_name         --派件员
     , net1.is_virtual_agent                                                           as is_virtual_agent      --1是虚拟代理区,2不是
     , nvl(t1.work_cnt, 0)                                                             as work_cnt              --普通(呼叫中心)客诉量
     , nvl(t1.project_cnt, 0)                                                          as project_cnt           --平台客诉量
     , nvl(t1.appeal_cnt, 0)                                                           as appeal_cnt            --邮政客诉量
     , nvl(t1.claim_cnt, 0)                                                            as claim_cnt             --理赔客诉量
     , nvl(third_platform_cnt, 0)                                                      as third_platform_cnt    --第三方客诉量
     , nvl(transfer_cnt_total, 0)                                                      as transfer_cnt          --转运客诉量
     , nvl(site_cnt, 0)                                                                as network_cnt           --网点客诉量
     , nvl(work_cnt, 0) + nvl(project_cnt, 0) + nvl(appeal_cnt, 0) + nvl(claim_cnt, 0) as all_cnt               --4种工单量相加
     , nvl(t6.taking_cnt, 0)                                                           as taking_cnt            --网点收件量
     , nvl(t6.arrival_cnt, 0)                                                          as arrival_cnt           --网点到件量
     , nvl(t1.xijia_sign_num, 0)                                                       as xijia_sign_num        --派件员普通工单虚假签收
     , nvl(t1.over_time_num, 0)                                                        as over_time_num         --派件员普通工单超时效催件
     , nvl(t1.have_take_no_send_num, 0)                                                as have_take_no_send_num --派件员普通工单有揽未发
     , nvl(t1.no_send_door_num, 0)                                                     as no_send_door_num      --派件员普通工单不送货上门
     , nvl(t1.service_attitude_num, 0)                                                 as service_attitude_num  --派件员普通工单服务态度
     , nvl(t1.money_problem_num, 0)                                                    as money_problem_num     --派件员普通工单费用问题
     , nvl(t1.out_complaint_num, 0)                                                    as out_complaint_num     --派件员普通工单外投诉意向
     , nvl(t1.compensate_num, 0)                                                       as compensate_num        --派件员普通工单赔偿意向
     , nvl(t1.reback_transfer_num, 0)                                                  as reback_transfer_num   --派件员普通工单退转件票数
     , nvl(t4.arr_ctn, 0)                                                              as center_op             --中心操作量
     , nvl(t5.effic_num, 0)                                                            as effic_num             --时效签收量
     , net1.dt
from net_whole net1
         left join merge1 t1
                   on net1.code = t1.network_code
                       and net1.dt = t1.dt
         left join
     (
         select scan_site_code
              , sum(oper_cnt) as arr_ctn --操作量
              , dt
         from jms_dm.dm_center_transfer_summary_dt
         where dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
           and dt <= '{{ execution_date | cst_ds }}'
           and sum_flag = '自然日'
         group by scan_site_code
                , dt
     ) t4
     on net1.code = t4.scan_site_code
         and net1.dt = t4.dt
         left join
     (
         select network_code
              , effic_num
              , dt
         from jms_dm.dm_false_sign_effic_sum_dt
         where dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
           and dt <= '{{ execution_date | cst_ds }}'
     ) t5
     on net1.code = t5.network_code
         and net1.dt = t5.dt
         left join
     (
         select network_code
              , taking_cnt
              , arrival_cnt
              , dt
         from jms_dm.dm_sqs_tab_take_arr_network_dt
         where dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
           and dt <= '{{ execution_date | cst_ds }}'
     ) t6
     on net1.code = t6.network_code
         and net1.dt = t6.dt
    distribute by net1.dt
;